package edu.ynu.se.xiecheng.achitectureclass.common.dao;

import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.List;
import java.util.Map;

/**
 * 数据访问对象（DAO）类，用于操作设备数据的持久化
 *
 * **设计模式：**
 * - **DAO 模式 (Data Access Object Pattern)**：封装数据库访问逻辑，提供与设备数据相关的操作方法。
 * - **单例连接模式**：每次访问数据库时都创建新的连接，建议在实际应用中使用连接池（如 HikariCP）以提高性能。
 *
 * **设计原则：**
 * - **单一职责原则 (SRP)**：类的职责仅限于设备数据的数据库操作。
 */
public class DeviceDataDao {

    // 数据库连接信息（建议使用配置文件管理）
    private static final String DB_URL = "jdbc:mysql://132.232.152.65:3306/20221120114?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
    private static final String DB_USER = "20221120114";
    private static final String DB_PASSWORD = "20221120114";

    /**
     * 根据传感器 ID 获取对应的表名
     *
     * **设计模式：**
     * - **简单工厂模式 (Simple Factory Pattern)**：根据传感器 ID 返回对应的表名。
     *
     * @param shebeiId 传感器 ID
     * @return 数据表名称
     */
    private String getTableNameBySensorId(String shebeiId) {
        switch (shebeiId) {
            case "327061330933584660":
                return "humidity_data";
            case "327061330933584661":
                return "temperature_data";
            case "327061330932536075":
                return "conductivity_data";
            case "327061330932536076":
                return "ph_data";
            case "327061330932536077":
                return "nitrogen_data";
            default:
                return null;
        }
    }

    /**
     * 将设备数据保存到数据库
     *
     * **事务管理：**
     * - 每条数据独立插入，数据库连接在 try-with-resources 中自动关闭。
     *
     * **优化建议：**
     * - 批量插入以提高性能。
     *
     * @param deviceData 包含设备数据的列表，每条数据是一个 Map
     */
    public void saveDataToDatabase(List<Map<String, Object>> deviceData) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
            for (Map<String, Object> data : deviceData) {
                String shebeiId = (String) data.getOrDefault("id", "");
                String boxId = (String) data.getOrDefault("boxId", "");
                String dataType = String.valueOf(data.getOrDefault("dataType", ""));
                String name = (String) data.getOrDefault("name", "");
                Double value = data.get("value") instanceof Number ? ((Number) data.get("value")).doubleValue() : null;
                Integer status = data.get("status") instanceof Number ? ((Number) data.get("status")).intValue() : null;
                Integer connState = data.get("connState") instanceof Number ? ((Number) data.get("connState")).intValue() : null;
                String connStateTimestampStr = (String) data.getOrDefault("connStateTimestamp", null);
                String timestampStr = (String) data.getOrDefault("timestamp", null);

                Timestamp connStateTimestamp = connStateTimestampStr != null ? convertToSqlTimestamp(connStateTimestampStr) : null;
                Timestamp sqlTimestamp = timestampStr != null ? convertToSqlTimestamp(timestampStr) : null;

                String tableName = getTableNameBySensorId(shebeiId);
                if (tableName != null) {
                    String sql = "INSERT INTO " + tableName +
                            " (shebei_id, box_id, data_type, name, value, status, conn_state, conn_state_timestamp, timestamp, create_time, update_time) " +
                            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW()) " +
                            "ON DUPLICATE KEY UPDATE " +
                            "data_type = VALUES(data_type), name = VALUES(name), value = VALUES(value), status = VALUES(status), " +
                            "conn_state = VALUES(conn_state), conn_state_timestamp = VALUES(conn_state_timestamp), " +
                            "timestamp = VALUES(timestamp), update_time = NOW()";

                    try (PreparedStatement stmt = connection.prepareStatement(sql)) {
                        stmt.setString(1, shebeiId);
                        stmt.setString(2, boxId);
                        stmt.setString(3, dataType);
                        stmt.setString(4, name);
                        stmt.setObject(5, value);
                        stmt.setObject(6, status);
                        stmt.setObject(7, connState);
                        stmt.setTimestamp(8, connStateTimestamp);
                        stmt.setTimestamp(9, sqlTimestamp);
                        stmt.executeUpdate();
                    }
                }
            }
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }

    /**
     * 将 ISO 时间字符串转换为 SQL 时间戳
     *
     * @param isoTimestamp ISO 格式的时间字符串
     * @return 转换后的 SQL 时间戳
     */
    private Timestamp convertToSqlTimestamp(String isoTimestamp) {
        try {
            Instant instant = Instant.parse(isoTimestamp);
            LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
            return Timestamp.valueOf(localDateTime);
        } catch (Exception e) {
            System.out.println("Error parsing timestamp: " + isoTimestamp + " - " + e.getMessage());
            return null;
        }
    }
}
